package com.yups.dao;

import com.yups.model.Coach;
import com.yups.model.Service;
import com.yups.model.ServiceType;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

/**
 * 服务数据操作类
 *
 * @author 于鹏生
 */
public class ServiceDao extends BaseDao implements ResultSetHandler<List<Service>> {
	private final String sql = "select SE.id seid, SE.subject sesubject, "
			+ " SE.photo sephoto, SE.coverpath secoverpath, "
			+ " SE.price seprice, SE.message semessage, "
			+ " SE.recommend serecommend,SE.detail  sedetail,"
			+ " C.id cid, C.company ccompany, "
			+ " C.photo cphoto, C.avatar cavatar, "
			+ " C.nickname cnickname, C.price cprice, "
			+ " C.message cmessage, C.LAT clat, C.LON clon,"
			+ " ST.id stid, ST.icon sticon,"
			+ " ST.title sttitle"
			+ " from service SE"
			+ " left join coach C on SE.COACH_ID = C.ID"
			+ " left join servicetype ST on SE.SERVICETYPE_ID = ST.ID";

	public boolean add(Service service) {
		try {
			String sql = "INSERT INTO service (\n"
					+ " SUBJECT,photo,coverpath,price,message,\n"
					+ " recommend,detail,coach_id,serviceType_id\n)\n"
					+ " VALUES (?,?,?,?,?,?,?,?,?)";
			return queryRunner.execute(sql,
					service.getSubject(),
					service.getPhoto(),
					service.getCoverpath(),
					service.getPrice(),
					service.getMessage(),
					service.getRecommend(),
					service.getDetail(),
					service.getCoach().getId(),
					service.getServiceType().getId()
			) == 1;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	public boolean delete(int id) {
		try {
			String sql = "DELETE FROM service WHERE id = ?";
			return queryRunner.execute(sql, id) == 1;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	public boolean update(Service service) {
		if (service.getCoverpath() == null || service.getPhoto() == null) {
			Service service1 = get(service.getId());
			if (service.getCoverpath() == null) {
				service.setCoverpath(service1.getCoverpath());
			}
			if (service.getPhoto() == null) {
				service.setPhoto(service1.getPhoto());
			}
		}
		try {
			String sql = "UPDATE `service` SET"
					+ " `SUBJECT` = ?,\n"
					+ " `photo` = ?,\n"
					+ " `coverpath` = ?,\n"
					+ " `price` = ?,\n"
					+ " `message` = ?,\n"
					+ " `recommend` = ?,\n"
					+ " `detail` = ?,\n"
					+ " `coach_id` = ?,\n"
					+ " `serviceType_id` = ?\n"
					+ " WHERE `id` = ?";
			return queryRunner.execute(sql,
					service.getSubject(),
					service.getPhoto(),
					service.getCoverpath(),
					service.getPrice(),
					service.getMessage(),
					service.getRecommend(),
					service.getDetail(),
					service.getCoach().getId(),
					service.getServiceType().getId(),
					service.getId()
			) == 1;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	public List<Service> getAll() {
		try {
			return queryRunner.query(sql, this);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public List<Service> getAll(int size, long page) {
		try {
			String temSql = sql + " order by seid LIMIT ?, ?";
			return queryRunner.query(temSql, this, (page - 1) * size, size);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public List<Service> getServiceByTypeID(int stid) {
		String tem = sql + " where ST.id = ?";
		try {
			return queryRunner.query(tem, this, stid);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public Service get(int id) {
		try {
			String idSql = " select id, subject, photo, coverpath, price,"
					+ " message, recommend, detail, coach_id, servicetype_id"
					+ " from service"
					+ " where id = ?";
			return queryRunner.query(idSql, rs -> {
				if (rs.next()) {
					return new Service(
							rs.getInt("id"),
							rs.getString("subject"),
							rs.getString("photo"),
							rs.getString("coverpath"),
							rs.getInt("price"),
							rs.getString("message"),
							rs.getString("recommend"),
							rs.getString("detail"),
							new Coach(rs.getInt("coach_id")),
							new ServiceType(rs.getInt("servicetype_id"))
					);
				}
				return null;
			}, id);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public List<Service> getServiceByCoachId(int cid) {
		try {
			String tem = sql + " where C.id = ?";
			return queryRunner.query(tem, this, cid);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public long getPageCount(int size) {
		try {
			String sql = "SELECT COUNT(1) FROM service";
			double ret = queryRunner.query(sql, new ScalarHandler<Long>());
			return (long) Math.ceil(ret / size);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}

	@Override
	public List<Service> handle(ResultSet rs) throws SQLException {
		List<Service> services = new LinkedList<>();
		while (rs.next()) {
			services.add(new Service(
					rs.getInt("seid"),
					rs.getString("sesubject"),
					rs.getString("sephoto"),
					rs.getString("secoverpath"),
					rs.getDouble("seprice"),
					rs.getString("semessage"),
					rs.getString("serecommend"),
					rs.getString("sedetail"),
					new Coach(
							rs.getInt("cid"),
							rs.getString("ccompany"),
							rs.getString("cphoto"),
							rs.getString("cavatar"),
							rs.getString("cnickname"),
							rs.getDouble("cprice"),
							rs.getString("cmessage"),
							rs.getDouble("clat"),
							rs.getDouble("clon")
					),
					new ServiceType(
							rs.getInt("stid"),
							rs.getString("sticon"),
							rs.getString("sttitle")
					)
			));
		}
		return services;
	}

	public Service getServiceByID(int id) {
		String temSql = " select id, subject, photo, coverpath, price,"
				+ " message, recommend, detail, coach_id, servicetype_id"
				+ " from service"
				+ " where id = ?";
		try {
			return queryRunner.query(temSql, new BeanHandler<>(Service.class), id);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
}
